package com.dai.chun.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.dai.chun.dao.dao.EmployeeDao;
import com.dai.chun.dao.model.Employee;
import com.dai.chun.dbc.DBManager;
import com.dai.chun.util.PageSplitUtil;

public class EmployeeDaoImpl implements EmployeeDao {
	public static void main(String[] args) {
		EmployeeDaoImpl employeeDaoImpl=new EmployeeDaoImpl();
		Employee employee = new Employee();
		employee.setEmp_birth(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
		try {
			employeeDaoImpl.save(employee);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private DBManager manager = DBManager.getInstance();
	

	public List<Employee> listEmployee(PageSplitUtil psu) {
		String sql = "select * from t_employee limit ?,?";
		List<Employee> employees = new ArrayList<Employee>();
		Connection conn = null;
		ResultSet res=null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt(1, psu.getStart());
			preparedStatement.setInt(2,psu.getEnd());
			res = preparedStatement.executeQuery();
			while (res.next()) {
				Employee employee = new Employee();
				employee.setEmp_id(res.getInt("emp_id"));
				employee.setEmp_num(res.getString("emp_num"));
				employee.setEmp_name(res.getString("emp_name"));
				employee.setEmp_sex(res.getString("emp_sex"));
				employee.setEmp_birth(res.getString("emp_borth"));
				employee.setEmp_edu(res.getString("emp_edu"));
				employee.setEmp_address(res.getString("emp_address"));
				employee.setEmp_tel(res.getString("emp_tel"));
				employee.setEmp_job_id(res.getInt("emp_job_id"));
				employee.setEmp_dept_id(res.getInt("emp_dept_id"));
				employee.setEmp_status(res.getInt("emp_status"));
				employee.setEmp_wage_base(res.getFloat("emp_wage_base"));
				employees.add(employee);
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement, res);
		}
		return employees;
	}

	public Boolean save(Employee employee) throws SQLException {
		boolean flag = false;
		String sql = "insert into t_employee (emp_num,emp_name,emp_password,emp_sex,emp_borth,emp_edu,emp_address,emp_tel,emp_job_id,emp_dept_id,emp_wage_base,emp_status) value (?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setString(1 ,employee.getEmp_num());
			preparedStatement.setString( 2,employee.getEmp_name());
			preparedStatement.setString( 3,employee.getEmp_password());
			preparedStatement.setString( 4,employee.getEmp_sex());
			preparedStatement.setString(5,employee.getEmp_birth());
			preparedStatement.setString(6 ,employee.getEmp_edu());
			preparedStatement.setString(7,employee.getEmp_address());
			preparedStatement.setString(8,employee.getEmp_tel());
			preparedStatement.setInt(9, employee.getEmp_job_id());
			preparedStatement.setInt( 10,employee.getEmp_dept_id());
			preparedStatement.setFloat( 11,employee.getEmp_wage_base());
			preparedStatement.setInt(12,employee.getEmp_status());
			int count = preparedStatement.executeUpdate(); // 
				if (count > 0) {
					flag = true;
				}
			} catch (Exception e) {
			} finally {
				manager.closeConn(conn, preparedStatement);
		}
		return flag;
	}

	@Override
	public int findAllCount() throws SQLException {
		int ret=0;
		String sql = "select count(*) from t_employee";
		Connection conn = manager.getConnection();
		java.sql.Statement sm=conn.createStatement();
		ResultSet rs=sm.executeQuery(sql);
		if(rs.next()){
			ret=rs.getInt(1);
		}
		manager.closeConn(conn, sm, rs);
		return ret;
	}

	@Override
	public int delEmployee(int emp_id) throws SQLException {
		int ret=0;
		String sql = "delete from t_employee where emp_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		preparedStatement.setInt(1 ,emp_id);
		ret=preparedStatement.executeUpdate();
		manager.closeConn(conn, preparedStatement);
		return ret;
		
	}

	@Override
	public int updateEmployee(Employee employee) throws SQLException {
		int ret=0;
		String sql = "update t_employee set emp_num=?,emp_name=?, emp_sex=?, emp_borth =?, emp_edu=?, emp_address=?, emp_tel=?, emp_job_id=?, emp_dept_id=?, emp_status where emp_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setString(1 ,employee.getEmp_num());
			preparedStatement.setString( 2,employee.getEmp_name());
			preparedStatement.setString( 3,employee.getEmp_sex());
			preparedStatement.setString( 4,employee.getEmp_birth());
			preparedStatement.setString(5 ,employee.getEmp_edu());
			preparedStatement.setString(6 ,employee.getEmp_address());
			preparedStatement.setString(7 ,employee.getEmp_tel());
			preparedStatement.setInt(8, employee.getEmp_job_id());
			preparedStatement.setInt( 9,employee.getEmp_dept_id());
			preparedStatement.setInt( 10,employee.getEmp_status());
			preparedStatement.setInt(11 ,employee.getEmp_id());
			int count = preparedStatement.executeUpdate(); // 
				if (count > 0) {
					ret=1;
				}
			} catch (Exception e) {
			} finally {
				manager.closeConn(conn, preparedStatement);
		}
		
		return ret;
	}

	@Override
	public Employee findEmployeeById(int emp_id) {
		String sql = "select * from t_employee where emp_id=?";
		Employee employee =new Employee();
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = manager.getConnection();
			preparedStatement =  manager.createPRPstmt(conn, sql);
			preparedStatement.setInt(1 ,emp_id);
			ResultSet res = preparedStatement.executeQuery();
			
			while (res.next()) {
				employee.setEmp_id(res.getInt("emp_id"));
				employee.setEmp_num(res.getString("emp_num"));
				employee.setEmp_name(res.getString("emp_name"));
				employee.setEmp_password(res.getString("emp_password"));
				employee.setEmp_sex(res.getString("emp_sex"));
				employee.setEmp_birth(res.getString("emp_borth"));
				employee.setEmp_edu(res.getString("emp_edu"));
				employee.setEmp_address(res.getString("emp_address"));
				employee.setEmp_tel(res.getString("emp_tel"));
				employee.setEmp_job_id(res.getInt("emp_job_id"));
				employee.setEmp_dept_id(res.getInt("emp_dept_id"));
				employee.setEmp_status(res.getInt("emp_status"));
				employee.setEmp_wage_base(res.getFloat("emp_wage_base"));
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement);
		}
		return employee;
	}

	@Override
	public List<Employee> searchWage(String sql) {
		List<Employee> employees = new ArrayList<Employee>();
		Connection conn = null;
		ResultSet res =null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			 res = preparedStatement.executeQuery();
			
			while (res.next()) {
				Employee employee = new Employee();
				employee.setEmp_id(res.getInt("emp_id"));
				employee.setEmp_num(res.getString("emp_num"));
				employee.setEmp_name(res.getString("emp_name"));
				employee.setEmp_sex(res.getString("emp_sex"));
				employee.setEmp_birth(res.getString("emp_borth"));
				employee.setEmp_edu(res.getString("emp_edu"));
				employee.setEmp_address(res.getString("emp_address"));
				employee.setEmp_tel(res.getString("emp_tel"));
				employee.setEmp_job_id(res.getInt("emp_job_id"));
				employee.setEmp_dept_id(res.getInt("emp_dept_id"));
				employee.setEmp_status(res.getInt("emp_status"));
				employee.setEmp_wage_base(res.getFloat("emp_wage_base"));
				employees.add(employee);
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement, res);
		}
		return employees;
	}

	@Override
	public Employee logIn(Employee emp) {
		String sql = "select * from t_employee where emp_name=?";
		Employee employee =new Employee();
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet res=null;
		try {
			conn = manager.getConnection();
			preparedStatement =  manager.createPRPstmt(conn, sql);
			preparedStatement.setString(1 ,emp.getEmp_name());
			res = preparedStatement.executeQuery();
			
			while (res.next()) {
				employee.setEmp_id(res.getInt("emp_id"));
				employee.setEmp_num(res.getString("emp_num"));
				employee.setEmp_password(res.getString("emp_password"));
				employee.setEmp_name(res.getString("emp_name"));
				employee.setEmp_sex(res.getString("emp_sex"));
				employee.setEmp_birth(res.getString("emp_borth"));
				employee.setEmp_edu(res.getString("emp_edu"));
				employee.setEmp_address(res.getString("emp_address"));
				employee.setEmp_tel(res.getString("emp_tel"));
				employee.setEmp_job_id(res.getInt("emp_job_id"));
				employee.setEmp_dept_id(res.getInt("emp_dept_id"));
				employee.setEmp_status(res.getInt("emp_status"));
				employee.setEmp_wage_base(res.getFloat("emp_wage_base"));
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement, res);
		}
		return employee;
	}

	@Override
	public int changePassword(Employee employee) throws SQLException {
		int ret=0;
		String sql = "update t_employee set emp_password=? where emp_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setString(1 ,employee.getEmp_password());
			preparedStatement.setInt(2 ,employee.getEmp_id());
			int count = preparedStatement.executeUpdate(); 
				if (count > 0) {
					ret=1;
				}
			} catch (Exception e) {
			} finally {
				manager.closeConn(conn, preparedStatement);
		}
		
		return ret;
		
	}

	@Override
	public List<Employee> listEmployeeByDeptId(int dept_id) {
		String sql = "select * from t_employee where emp_dept_id=?";
		List<Employee> employees = new ArrayList<Employee>();
		Connection conn = null;
		ResultSet res=null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt(1, dept_id);
			res = preparedStatement.executeQuery();
			while (res.next()) {
				Employee employee = new Employee();
				employee.setEmp_id(res.getInt("emp_id"));
				employee.setEmp_num(res.getString("emp_num"));
				employee.setEmp_name(res.getString("emp_name"));
				employee.setEmp_sex(res.getString("emp_sex"));
				employee.setEmp_birth(res.getString("emp_borth"));
				employee.setEmp_edu(res.getString("emp_edu"));
				employee.setEmp_address(res.getString("emp_address"));
				employee.setEmp_tel(res.getString("emp_tel"));
				employee.setEmp_job_id(res.getInt("emp_job_id"));
				employee.setEmp_dept_id(res.getInt("emp_dept_id"));
				employee.setEmp_status(res.getInt("emp_status"));
				employee.setEmp_wage_base(res.getFloat("emp_wage_base"));
				employees.add(employee);
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement, res);
		}
		return employees;
	}

	@Override
	public List<Employee> listAllEmployee() {
		String sql = "select * from t_employee";
		List<Employee> employees = new ArrayList<Employee>();
		Connection conn = null;
		ResultSet res=null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			res = preparedStatement.executeQuery();
			while (res.next()) {
				Employee employee = new Employee();
				employee.setEmp_id(res.getInt("emp_id"));
				employee.setEmp_num(res.getString("emp_num"));
				employee.setEmp_name(res.getString("emp_name"));
				employee.setEmp_password(res.getString("emp_password"));
				employee.setEmp_sex(res.getString("emp_sex"));
				employee.setEmp_birth(res.getString("emp_borth"));
				employee.setEmp_edu(res.getString("emp_edu"));
				employee.setEmp_bank_account(res.getString("emp_bank_account"));
				employee.setEmp_address(res.getString("emp_address"));
				employee.setEmp_tel(res.getString("emp_tel"));
				employee.setEmp_job_id(res.getInt("emp_job_id"));
				employee.setEmp_dept_id(res.getInt("emp_dept_id"));
				employee.setEmp_dept(res.getString("emp_dept"));
				employee.setEmp_job(res.getString("emp_job"));
				employee.setEmp_sxyj(res.getInt("emp_sxyj"));
				employee.setEmp_status(res.getInt("emp_status"));
				employee.setEmp_wage_base(res.getFloat("emp_wage_base"));
				employees.add(employee);
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement, res);
		}
		return employees;
	}

}
